import { execute, select } from '../../utils/db';
import type { Trainer } from '../../types/trainer';
import { Page, PageReq } from '../../types/page';

export class TrainerDao {
  // 新增患者
  static async create(trainer: Omit<Trainer, 'id'>) : Promise<number | undefined> {
    const sql = `INSERT INTO trainer (
      name, gender, age, birthday, family_structure, 
      contact, tel, address, disease, medical_history,
      check_items, check_result, train_doctor, charge_doctor,create_time
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)`;
    
    const params = [
      trainer.name, trainer.gender, trainer.age, trainer.birthday, trainer.family_structure,
      trainer.contact, trainer.tel, trainer.address, trainer.disease, trainer.medical_history,
      trainer.check_items, trainer.check_result, trainer.train_doctor, trainer.charge_doctor,trainer.create_time
    ];

    return (await execute(sql, params)).lastInsertId;
  }

  // 更新患者信息
  static async update(trainer: Partial<Trainer>) {
    const fields = [];
    const params = [];
    
    for (const [key, value] of Object.entries(trainer)) {
      if(key === 'trainer_files'){
        continue;
      }
      fields.push(`${key} = ?`);
      params.push(value);
    }
    
    if (fields.length === 0) {
      return Promise.resolve();
    }
    
    params.push(trainer.id);
    const sql = `UPDATE trainer SET ${fields.join(', ')} WHERE id = ?`;
    return execute(sql, params);
  }

  // 删除患者
  static async delete(id: number) {
    const sql = 'DELETE FROM trainer WHERE id = ?';
    return execute(sql, [id]);
  }

  // 根据ID查询患者
  static async findById(id: number): Promise<Trainer | null> {
    const sql = 'SELECT * FROM trainer WHERE id = ?';
    const result = await select<Trainer[]>(sql, [id]);
    return result.length > 0 ? result[0] : null;
  }

  // 查询所有患者
  // 执行原始SQL
  static async execute(sql: string, params?: any[]) {
    return execute(sql, params);
  }

  // 查询所有患者(支持自定义SQL)
  static async list(): Promise<Trainer[]> {
    const query = 'SELECT * FROM trainer';
    return await select<Trainer[]>(query);
  }

  static async countTrainer(){
    const sql = 'SELECT COUNT(1) as count FROM trainer'
    const result = await select<{count:number}>(sql)
    return result[0].count
  }
  /**
   * 分页查询患者列表
   */
  static async page(params: PageReq<Trainer>): Promise<Page<Trainer>> {
    const { pageNum, pageSize } = params
    const offset = (pageNum - 1) * pageSize
    const fields = []
    const values = []
    const trainer = params.params
    
    if (trainer.name) {
      fields.push('name like ?')
      values.push(`%${trainer.name}%`)
    }
    if (trainer.tel) {
      fields.push('tel like ?')
      values.push(`%${trainer.tel}%`)
    }
    if (trainer.disease) {
      fields.push('disease like ?')
      values.push(`%${trainer.disease}%`)
    }
    //gender
    if(trainer.gender){
      fields.push('gender = ?')
      values.push(trainer.gender)
    }

    const condition = fields.length > 0 ? `WHERE ${fields.join(' AND ')}` : ''
    const countSql = `SELECT COUNT(*) as total FROM trainer ${condition}`
    const listSql = `SELECT * FROM trainer ${condition} LIMIT ${pageSize} OFFSET ${offset}`
    
    const totalResult = await select<{total: number}>(countSql, values)
    const listResult = await select<Trainer[]>(listSql, values)
    
    return {
      total: totalResult[0].total || 0,
      list: listResult || []
    }
  }
}